package com.stargate.bd.dao.factory;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import com.stargate.bd.bean.Badge;
import com.stargate.bd.bean.Etat;
import com.stargate.bd.dao.DAO;

public class BadgeDAO extends DAO<Badge> {

	@Override
	public ArrayList<Badge> selectAll() throws SQLException {
		ArrayList<Badge> badges = new ArrayList<Badge>();
		String sql = "select * from badge";
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sql);
		while (result.next()) {
			badges.add(new Badge(result.getInt(Badge.NUM_BADGE),
					Etat.valueOf(result.getString(Badge.ETAT_BADGE))));
		}
		result.close();
		statement.close();
		return badges;
	}

	@Override
	public Badge select(Badge bean) throws SQLException {
		// TODO Auto-generated method stub
		throw new UnsupportedOperationException("Not yet implemented");
	}

	@Override
	public void update(Badge bean) throws SQLException {
		String sql = "UPDATE " + Badge.TABLE_NAME +
				" SET " + Badge.ETAT_BADGE + "=?" +
				" WHERE " + Badge.NUM_BADGE + "=?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setObject(1, bean.getEtatBadge().getEtat(), Types.VARCHAR);
		statement.setObject(2, bean.getNumBadge(), Types.INTEGER);
		statement.executeUpdate();
		statement.close();
	}

	@Override
	public void delete(Badge bean) throws SQLException {
		// TODO Auto-generated method stub
		throw new UnsupportedOperationException("Not yet implemented");
	}

	@Override
	public void insert(Badge bean) throws SQLException {
		System.out.println("*** Insertion de " + bean + "***");
		String sql = "INSERT INTO " + Badge.TABLE_NAME + " VALUES (seq_NumBadge.NEXTVAL,?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setObject(1, bean.getEtatBadge().getEtat(), Types.VARCHAR);
		statement.executeUpdate();
		statement.close();
	}

	public int getSeqNumBadge() throws SQLException {
		String sql =  "SELECT seq_NumBadge.currval FROM DUAL";
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sql);
		int seqNum = -1;
		if (result.next()) {
			seqNum = result.getInt(1);
		}
		result.close();
		statement.close();
		return seqNum;
	}
	
	public ArrayList<Badge> getBadgesNonAffectes() throws SQLException {
		ArrayList<Badge> badges = new ArrayList<Badge>();
		String sql = "select * from badge " +
				"where numbadge not in (" +
				"select numbadge from affectation)";
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sql);
		while (result.next()) {
			badges.add(new Badge(result.getInt(Badge.NUM_BADGE),
					Etat.valueOf(result.getString(Badge.ETAT_BADGE))));
		}
		result.close();
		statement.close();
		return badges;
	}
	
	public int getMaxNumBadge() throws SQLException {
		String sql = "SELECT MAX(" + Badge.NUM_BADGE + ") FROM " + Badge.TABLE_NAME;
		System.out.println(sql);
		int numBadge = -1;
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sql);
		if (result.next()) {
			numBadge = result.getInt(1);
		}
		result.close();
		statement.close();
		return numBadge;
	}

}
